"""
Case Type   : 服务端工具
Case Name   : 脏页数为0时停止数据库成功
Create At   : 2024/10/23
Owner       : @xiaqi14
Description :
    1.设置参数
    2.执行sql语句
    3.checkpoint执行
    4.停止数据库
    5.恢复主备成功，清理资源成功
Expect      :
    1.设置参数成功
    2.执行sql语句成功
    3.checkpoint执行成功，脏页数为0
    4.停止数据库成功
    5.恢复主备成功，清理资源成功
History     :
"""
import os
import unittest
import time
from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.Common import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

COMMONSH = CommonSH("PrimaryDbUser")


@unittest.skipIf(3 != COMMONSH.get_node_num(), '非1+2环境不执行')
class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.Constant = Constant()
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.primary_node = Node('PrimaryDbUser')
        self.env_path = macro.DB_ENV_PATH
        self.sta_node = Node('Stabdby1DBuser')
        self.dn_path = macro.DB_INSTANCE_PATH
        self.standby_sh = CommonSH('Stabdby1DBuser')

    def test_gs_om(self):
        text = '----step1:设置参数 expect:成功----'
        self.log.info(text)
        excute_cmd = f'''
                    source {self.env_path}
                    gs_guc reload -N all -I all -c "log_pagewriter = on"
                    gs_guc reload -N all -I all -c "bgwriter_delay = 10s"
                    '''
        self.log.info(excute_cmd)
        msg = self.pri_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.asserEqual(2, msg.count(self.Constant.GSGUC_SUCCES_MSG), '执行失败' + text)

        text = '----step2:执行sql语句 expect:成功----'
        self.log.info(text)
        sql_cmd = f'''
                    declare
                        tb_name text;
                        begin
                            for i in 0..100 loop
                                tb_name := 't_gs_om_0120_' || i;
                                execute 'drop table if exists ' || tb_name || ';';
                                execute 'create table ' || tb_name ||'
                                (id1 varchar unique,id2 int primary key,id3 int);';
                                execute 'insert into '
                                || tb_name 
                                || ' values(generate_series(1,5000),
                                generate_series(1,5000),generate_series(1,5000));';
                            end loop;
                        end; 
                    '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('ANONYMOUS BLOCK EXECUTE', result, '执行失败' + text)

        text = '----step3:checkpoint执行成功，脏页数为0 expect:成功----'
        self.log.info(text)
        sql_cmd = f'''
                   checkpoint;
                   select remain_dirty_page_num 
                   from dbe_perf.global_pagewriter_status;
                    '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('CHECKPOINT', result, '执行失败' + text)
        self.assertIn('0', result, '执行失败' + text)

        text = '----step4 停止数据库 expect:成功----'
        self.log.info(text)
        excute_cmd = f'''
                        source {self.env_path}
                        gs_om  -t  stop
                     '''
        self.log.info(excute_cmd)
        msg = self.pri_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.asserIn(self.Constant.GS_OM_STOP_SUCCESS_MSG, msg, '执行失败' + text)

    def tearDown(self):
        text = "----step5：恢复环境 expect:成功----"
        self.log.info(text)
        excute_cmd = f'''
                        source {self.env_path}
                        gs_om  -t  restart
                     '''
        self.log.info(excute_cmd)
        msg = self.pri_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.asserIn('Successfully started', msg,'执行失败' + text)
        sql_cmd = f'''
                    declare
                        tb_name text;
                        begin
                            for i in 0..100 loop
                                tb_name := 't_gs_om_0120_' || i;
                                execute 'drop table if exists ' || tb_name || ';';
                                execute 'create table ' || tb_name ||'
                                (id1 varchar unique,id2 int primary key,id3 int);';
                                execute 'insert into '
                                || tb_name 
                                || ' values(generate_series(1,5000),
                                generate_series(1,5000),generate_series(1,5000));';
                            end loop;
                        end; 
                    '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('ANONYMOUS BLOCK EXECUTE', result, '执行失败' + text)
        excute_cmd = f'''
                    source {self.env_path}
                    gs_guc reload -N all -I all -c "log_pagewriter = off"
                    gs_guc reload -N all -I all -c "bgwriter_delay = 2s"
                    '''
        self.log.info(excute_cmd)
        msg = self.pri_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.asserEqual(2, msg.count(self.Constant.GSGUC_SUCCES_MSG), '执行失败' + text)
        self.log.info(f'----{os.path.basename(__file__)} end----')
